I've noticed the following....

Create a sheet with the values below and enable the "Filter" option on this table.
(I've added the row-numbers on the left, to later show the filter in action)

(1) col1 col2
(2) a 1
(3) a 2
(4) a 3

Using the filter option on the col2 column, unmark value "2".
This is an essential step, since the bug only appears when hiding rows -in between- of other rows.
Hiding 1 or 3 will not have the same result.
You table should now look like this. Notice row (3) is hidden, as it should be:

(1) col1 col2
(2) a 1
(4) a 3

Now type "b" in a random empty cell, select the cell (not the value), and copy it.

Now, and this is an essential step, click down on the "a" value on row (2), keeping your mouse pressed, drag to the "a" value on row (4),  so that both cells are selected with one selection box, not two separate boxes.

Within this selection box, right-mouse click, and select "Paste Special -> Values" NOT "Paste Special -> Paste".

Now use the filter to unhide value "2". You table should/will look like this:

(1) col1 col2
(2) b 1
(3) b 2
(4) b 3

This is -not- the expected behaviour, when using "Paste Special -> Paste", the result is as expected:

(1) col1 col2
(2) b 1
(3) a 2
(4) b 3

This issue is also present in Excel 2010, and i've tested this on various computers.

• Edited by Wouter1231 21 hours 40 minutes ago typo

Hey!

Thanks for posting this, that's really useful information. I didn't know about the Find&Select feature.

Yet, it doesn't quite declassifies this as a bug, since, if overwriting all the hidden cells is considered the desired behaviour (which i find a dangerous default to use), then why -doesn't- the normal "Paste" option -also- overwrite the hidden fields?

Following your line of reasoning, the Paste Special is working properly, but the "Paste" method contains a bug. The point that their behaviour is inconsistent remains, even though you have provided a great workaround.

Regards,

Wouter

The point that their behaviour is inconsistent remains

I don't let those things worry me. I just treat them as something to be aware of.

Software has become so complex that it is unlikely that all of the idiosyncrasies will ever be ironed out and it is good that when someone like yourself finds one that they share it on the forums.